package com.bw.dao;

import com.bw.pojo.Customer;
import com.bw.util.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/***************************
 FileAction: 这个类 就是对数据库中 Customer表进行增删改查操作的

 考试结束以后 上交 4个文件  全程录屏  代码  效果演示录屏  技能文件(word文档 里面标注里  哪些完成 哪些未完成)



 CreateTime:2022-05-05 8:33
 Author:Aaking-aline
 ****************************/
public class CustomerDao {

    public void insertCustomer(Customer cus) throws SQLException {
        String sql = "insert into customer values(null,?,?,?)";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,cus.getName());
        ps.setObject(2,cus.getAge());
        ps.setObject(3,cus.getAddress());

        ps.executeUpdate();
    }

    // 参数类型到底是用 Integer 还是 String  主要看 调用这个方法的位置 要传递什么类型
    public void deleteCustomerById(String id) throws SQLException {
        String sql = "delete from customer where id = ?";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,id);

        ps.executeUpdate();
    }



    public List<Customer> selectAllCustomer() throws SQLException {
        String sql = "select * from customer";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Customer> list = new ArrayList<>();

        while(rs.next()){
            Customer customer = new Customer(rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getString(4));
            list.add(customer);
        }

        return list;
    }

    public List<Customer> selectAllCustomerByLimit(Integer page) throws SQLException {

        Integer index = (page - 1) * 3;

        String sql = "select * from customer limit ?,3";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);


        ps.setObject(1,index);


        ResultSet rs = ps.executeQuery();

        List<Customer> list = new ArrayList<>();

        while(rs.next()){
            Customer customer = new Customer(rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getString(4));
            list.add(customer);
        }

        return list;
    }

    public List<Customer> selectAllCustomerByLike(String str) throws SQLException {

        String sql = "select * from customer where name like '%" + str + "%'";
        //String sql = "select * from customer where name like '%张%'";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Customer> list = new ArrayList<>();

        while(rs.next()){
            Customer customer = new Customer(rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getString(4));
            list.add(customer);
        }

        return list;
    }


    public void updateCustomer(Customer cus) throws SQLException {
        String sql = "update customer set name = ?,age = ?,address = ? where id = ?";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,cus.getName());
        ps.setObject(2,cus.getAge());
        ps.setObject(3,cus.getAddress());
        ps.setObject(4,cus.getId());

        ps.executeUpdate();
    }





}
